package ru.gerunn.study.spring.jdbctemplate;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;

public class Main {

	public static void main(String[] args) {

		final org.apache.log4j.Logger logger = org.apache.log4j.Logger.getLogger(Main.class);

		@SuppressWarnings("resource")
		final ApplicationContext ac = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
		logger.info("");

		final JdbcTemplate jdbcTemplate = (JdbcTemplate) ac.getBean("template");
		logger.info(jdbcTemplate);

		jdbcTemplate.execute(new ConnectionCallback<Object>() {

			public Object doInConnection(Connection conn) throws SQLException, DataAccessException {

				DatabaseMetaData databaseMetaData = conn.getMetaData();
				ResultSet rs = databaseMetaData.getTables(null, null, "testtable", new String[] { "TABLE" });

				if (rs.next()) {
					logger.info("table exists. fuck you...");
				} else {
					logger.info("table doesn't exists. creating...");
					jdbcTemplate.update("create table testtable (id int primary key not null, string varchar(32))");
				}

				rs.close();

				return null;
			}

		});

		final int rowCount = jdbcTemplate.queryForInt("select count(*) from testtable");
		logger.info("current rows count: " + rowCount);
		final Integer nextValue = rowCount + 1;

		jdbcTemplate.update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement("insert into testtable (id, string) values (?, ?)");
				ps.setInt(1, nextValue);
				ps.setString(2, nextValue.toString() + " by simple insert");
				return ps;
			}

		});

		jdbcTemplate.batchUpdate("insert into testtable (id, string) values (?, ?)", new BatchPreparedStatementSetter() {

			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Integer integer = nextValue + i + 1;
				ps.setInt(1, integer);
				ps.setString(2, integer.toString() + " by batch insert");
			}

			public int getBatchSize() {
				return 2;
			}

		});

		jdbcTemplate.query("select id, string from testtable", new RowCallbackHandler() {

			public void processRow(ResultSet rs) throws SQLException {
				Integer id = rs.getInt("id");
				String string = rs.getString("string");
				logger.info("id: " + id + ",\tstring: " + string);
			}

		});

	}

}
